package window;

import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;

import javax.swing.DefaultCellEditor;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JProgressBar;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableCellEditor;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;

import jxl.Sheet;
import jxl.Workbook;
import util.ArrayTransfer;
import util.ProgressThread;
import Itransfer.Itransfer;

/**
 * operation windows , do user settings
 * 
 * @author Patrick
 * @date 2010.3.1
 */
public class MainFrame extends JFrame implements ActionListener {
    public static final int DB_SEG = 0;

    public static final int XLS_SEG = 1;

    public static final int PREVIEW_LINES = 30;

    public static final int UNIT_TIME = 100;

    public Itransfer transfer;

    Connection cn;

    Statement statement;

    JButton chooseFile, doImport;

    JLabel dbname, tname, excel, sheetname, startLabel, endLabel;

    JTextField start, end;

    JComboBox databaseBox, tableBox, sheetBox, cellBox;

    JTable dbtable, exceltable;

    DefaultTableModel tableModel;

    JFileChooser chooser;

    JPanel set, showDb, preview, operator;

    JScrollPane dbPane, excelPane;

    JProgressBar progressBar;

    // listener
    ActionListener tableItemListener, sheetActionListener;

    // excel elements !!
    Workbook book;

    Sheet sheet;

    int startLine = 5;

    int endLine = 10;

    // construction method
    MainFrame(Map<String, String> map, String winName) throws Exception {
        super(winName);
        setBounds(200, 200, 600, 600);
        setLayout(null);
        /**
         * override window listener
         */
        addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                try {
                    statement.close();
                    cn.close();
                } catch (SQLException eee) {
                    System.err.println("Warning:Database disconnecting failed...");
                    eee.printStackTrace();

                }
                System.exit(0);
            }
        });
        /**
         * window listerner end 
         */
        String[] databases = null;
        try {
            // connect database
            Class.forName(map.get("driver"));// the driver class must be put in class path , can not use absolute path
            cn = DriverManager.getConnection(map.get("serverUrl"), map.get("user"), map.get("pwd"));
            System.out.println("***********connection complete*************");
            // load database info
            statement = cn.createStatement();
            Class tfClass = Class.forName(map.get("xlsTransfer"));
            transfer = (Itransfer) tfClass.newInstance();
            /**
             * since databases like oracle can't find database name list, here we use try and if can't find,simply use
             * "Default"
             */
            try {
                ResultSet result = statement.executeQuery(transfer.showDatabase());
                ArrayList<String> list = new ArrayList<String>();
                while (result.next()) {
                    System.out.println(result.getString(1));
                    list.add(result.getString(1));
                }
                databases = ArrayTransfer.transString(list);
            } catch (Exception e) {
                System.err.println("Database names INVALID....Useing default");
                databases = new String[] { "Default" };
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("Class not found.....");
        } catch (Exception e) {
            System.out.println("Connecting failed..-_-");
            e.printStackTrace();
        }
        /**
         * window construction
         */
        set = new JPanel();
        showDb = new JPanel();
        /**
         * set panel
         */
        add(set);
        set.setBounds(0, 10, 600, 120);
        set.setLayout(null);
        databaseBox = new JComboBox(databases);
        ResultSet rs = statement.executeQuery(transfer.showTables(databases[0]));
        ArrayList<String> list1 = new ArrayList<String>();
        while (rs.next()) {
            System.out.println(rs.getString(1));
            list1.add(rs.getString(1));
        }
        tableBox = new JComboBox(ArrayTransfer.transString(list1));
        set.add(dbname = new JLabel("数据库"));
        dbname.setBounds(10, 15, 50, 30);
        set.add(databaseBox);
        databaseBox.addActionListener(new DataBaseActionListener());
        databaseBox.setBounds(61, 15, 159, 30);
        set.add(tname = new JLabel("Table"));
        tname.setBounds(240, 15, 50, 30);
        set.add(tableBox);
        tableBox.addActionListener(tableItemListener = new TableItemListener());// itemListener
        tableBox.setBounds(291, 15, 160, 30);
        set.add(excel = new JLabel("数据源EXCEL文件:"));
        excel.setBounds(10, 60, 150, 30);
        set.add(chooseFile = new JButton("选择"));
        chooseFile.setBounds(160, 60, 70, 30);
        chooseFile.addActionListener(this);
        set.add(sheetname = new JLabel("Sheet"));
        sheetname.setBounds(240, 60, 50, 30);
        sheetBox = new JComboBox();
        sheetBox.setEnabled(false);
        set.add(sheetBox);
        sheetBox.setBounds(291, 60, 160, 30);
        sheetBox.addActionListener(sheetActionListener = new SheetActionListener());
        /**
         * showDb panel setting
         */
        cellBox = new JComboBox(new String[] { "N/A" });
        add(showDb);
        showDb.setBounds(0, 131, 600, 200);
        showDb.setLayout(null);
        showDb.setBackground(Color.BLACK);
        dbPane = new JScrollPane(dbtable);// here dbtable is null，no data initialized
        showDb.add(dbPane);
        dbPane.setBounds(50, 5, 500, 190);
        /**
         * preview panel setting
         */
        preview = new JPanel();
        add(preview);
        preview.setBounds(0, 331, 600, 200);
        preview.setLayout(null);
        excelPane = new JScrollPane(exceltable);
        preview.add(excelPane);
        excelPane.setBounds(50, 5, 450, 190);
        preview.add(startLabel = new JLabel("起始行："));
        preview.add(endLabel = new JLabel("结束行："));
        preview.add(start = new JTextField());
        preview.add(end = new JTextField());
        startLabel.setBounds(510, 10, 80, 30);
        start.setBounds(510, 50, 50, 30);
        endLabel.setBounds(510, 90, 80, 30);
        end.setBounds(510, 130, 50, 30);
        /**
         * operator panel setting
         */
        operator = new JPanel();
        add(operator);
        operator.setBounds(0, 531, 600, 70);
        operator.setLayout(null);
        operator.setBackground(Color.BLACK);
        operator.add(doImport = new JButton("导入"));
        doImport.setBounds(160, 7, 80, 30);
        doImport.addActionListener(new ImportActionListener());
        progressBar = new JProgressBar(0, 100);
        operator.add(progressBar);
        progressBar.setBounds(280, 7, 100, 30);
        progressBar.setStringPainted(true);
        setVisible(true);
        setResizable(false);
    }

    /**
     * action listener for file choose window , though I'm not willing to write like this
     */
    public void actionPerformed(ActionEvent e) {
        int retval;
        chooser = new JFileChooser();
        chooser.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);// 设置选择模式，既可以选择文件又可以选择文件夹
        String exc[] = { "xlsx", "xls" };
        FileFilter filter = new FileNameExtensionFilter("MS Excel", exc);
        chooser.setFileFilter(filter);// 设置文件后缀过滤器
        retval = chooser.showOpenDialog(this);// 显示"打开"对话框
        if (retval == JFileChooser.APPROVE_OPTION) {// 若成功打开
            File file = chooser.getSelectedFile();// 得到选择的文件名
            try {
                book = Workbook.getWorkbook(file);
                // 将页名装入下拉框
                sheetBox.removeActionListener(sheetActionListener);// remove listener,so that item changing does not
                // matter
                for (int i = 0; i < book.getNumberOfSheets(); i++) {
                    sheetBox.addItem(book.getSheet(i).getName());
                }
                sheetBox.addActionListener(sheetActionListener);
                sheetBox.setEnabled(true);
            } catch (Exception xlse) {
                xlse.printStackTrace();
            }
        }
    }

    // the listener for database combobox
    class DataBaseActionListener implements ActionListener {
        public void actionPerformed(ActionEvent e) {
            System.out.println("***" + databaseBox.getSelectedItem().toString() + "**");
            try {
                ResultSet rs = statement.executeQuery(transfer.showTables(databaseBox.getSelectedItem().toString()));
                tableBox.removeActionListener(tableItemListener);
                tableBox.removeAllItems();
                while (rs.next()) {
                    tableBox.addItem(rs.getString(1));
                }
                tableBox.addActionListener(tableItemListener);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
    }

    // the listener for table combobox
    class TableItemListener implements ActionListener {
        public void actionPerformed(ActionEvent e) {
            System.out.println("***TableBox Listener fired!!!!****");
            if (tableBox != null && tableBox.getSelectedItem() != null) {
                String[] columNames = { tableBox.getSelectedItem().toString() + "表字段", "Excel对应列" };
                String[][] tdata = null;
                // get table columns
                try {
                    ResultSet rs = statement.executeQuery(transfer.showTitles(databaseBox.getSelectedItem().toString(),
                            tableBox.getSelectedItem().toString()));
                    ArrayList<String> cl = new ArrayList<String>();
                    while (rs.next()) {
                        cl.add(rs.getString(1));
                    }
                    tdata = new String[cl.size()][2];
                    for (int i = 0; i < cl.size(); i++) {
                        tdata[i][0] = cl.get(i);
                        tdata[i][1] = "N/A";
                    }
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
                // construct table
                // TODO each time select a table , renew a totally different table
                showDb.remove(dbPane);
                tableModel = new DefaultTableModel(tdata, columNames);
                dbtable = new JTable(tableModel);
                // install cell editor
                TableCellEditor select = new DefaultCellEditor(cellBox);
                TableColumnModel columnModel = dbtable.getColumnModel();
                TableColumn excelColumn = columnModel.getColumn(XLS_SEG);// table column index begins from 0
                excelColumn.setCellEditor(select);
                dbPane = new JScrollPane(dbtable);
                showDb.setBackground(Color.BLACK);
                showDb.add(dbPane);
                dbPane.setBounds(50, 5, 500, 190);
                showDb.validate();// !!使之有效，否则不会立刻变成改变的样式，类似于刷新？
            }
        }
    }

    // the listener for sheet combobox
    class SheetActionListener implements ActionListener {
        public void actionPerformed(ActionEvent e) {
            sheet = book.getSheet(sheetBox.getSelectedItem().toString());
            String[] columnNames = new String[sheet.getColumns() + 1];
            columnNames[0] = "";
            String[][] tdata = new String[PREVIEW_LINES][sheet.getColumns() + 1];
            // set cell editor combobox
            cellBox.removeAllItems();
            cellBox.addItem("N/A");
            for (int i = 0; i < sheet.getColumns(); i++) {
                cellBox.addItem(String.valueOf((char) (65 + i)));
                columnNames[i + 1] = String.valueOf((char) (65 + i));// set table title by the way
            }
            // set excel data
            for (int i = 0; i < PREVIEW_LINES; i++) {
                tdata[i][0] = String.valueOf(i);
                for (int j = 1; j < sheet.getColumns() + 1; j++) {
                    tdata[i][j] = sheet.getCell(j - 1, i).getContents();
                }
            }
            preview.remove(excelPane);
            exceltable = new JTable(tdata, columnNames);
            excelPane = new JScrollPane(exceltable);
            preview.add(excelPane);
            excelPane.setBounds(50, 5, 450, 190);
            preview.validate();
        }
    }

    // the listener for import button
    class ImportActionListener implements ActionListener {
        public void actionPerformed(ActionEvent e) {
            doImport.setEnabled(false);
            progressBar.setValue(0);
            startLine = Integer.valueOf(start.getText());
            endLine = Integer.valueOf(end.getText());
            ArrayList<String> segNames = new ArrayList<String>();// database segment
            ArrayList<Integer> excelSeg = new ArrayList<Integer>();// excel column
            ArrayList<String> values = new ArrayList<String>();// each row of excel values
            for (int i = 0; i < tableModel.getRowCount(); i++) {
                if (!tableModel.getValueAt(i, XLS_SEG).toString().equals("N/A")) {
                    segNames.add(tableModel.getValueAt(i, DB_SEG).toString());
                    excelSeg.add(Integer
                            .valueOf((int) (tableModel.getValueAt(i, XLS_SEG).toString().toCharArray()[0]) - 65));
                }
            }
            // progress simulation thread start~
            Thread progressThread = new Thread(new ProgressThread(progressBar, endLine - startLine, UNIT_TIME));
            progressThread.start();

            for (int j = startLine; j < endLine; j++) {// sheet.getRows()
                values.clear();
                // get values of Row j
                for (int k = 0; k < excelSeg.size(); k++) {
                    values.add("\'" + sheet.getCell(excelSeg.get(k).intValue(), j).getContents() + "\'");
                    System.out.println("location:" + excelSeg.get(k) + "列" + j + "行，"
                            + sheet.getCell(excelSeg.get(k).intValue(), j).getContents());
                }
                // import
                try {
                    statement.execute(transfer.insert(segNames, values, databaseBox.getSelectedItem().toString(),
                            tableBox.getSelectedItem().toString()));
                } catch (Exception exception) {
                    System.err.println("inserting error....");
                    exception.printStackTrace();
                }
            }
            progressBar.setValue(100);
            doImport.setEnabled(true);
        }
    }
}
